with tab_barscan_bagging as (
   select *
    from (
  select waybill_no,   --运单号
     package_code,  --包号
     scan_type,     -- 扫描类型
     scan_site_code, --建包网点编码
     scan_site,      --建包网点
     scan_time      --包的扫描时间
        ,row_number() over (partition by waybill_no order by scan_time desc ) rn
  from jms_dwd.dwd_tab_barscan_bagging_base_dt t1
  join (select code ,name
  from jms_dim.dim_network_whole_massage
  where is_delete=1
    and is_enable=1
        and network_type in(4,5)
  ) network_detail
  on t1.scan_site_code=network_detail.code
  where dt>=date_sub('{{ execution_date | cst_ds }}',29)
  and dt<='{{ execution_date | cst_ds }}'
  and package_code like 'B4%'
  and scan_type='建包扫描'
    )tmp
  where tmp.rn=1
),

--三段码包牌表
  third_package_list as (
      select
         package_number, --包号
         second_package_code, --二段吗
         third_package_code,   --包三段码
         is_used,   --是否使用
         package_type,   --包类型 包签类型 1混 2派'
         center_code,     --拆包网点编码
         center_name      --拆包网点名称
      from jms_dwd.dwd_third_package_list_base_dt
      where dt>=date_sub('{{ execution_date | cst_ds }}',40)
      and dt<='{{ execution_date | cst_ds }}'
      and is_used !=0
  ),

--错分扫描表
   ass_address_audit as (
       select
          waybill_no, --运单号
          report_time, --错分上报时间
          report_network_code,  --错分上班网点code
          report_network_name,  --错分上报网点名字
          wrong_network_code,  --错分所属网点code
          wrong_network_name  --错分所属网点
      from jms_dwd.dwd_t_ass_address_audit_base_dt
      where dt>=date_sub('{{ execution_date | cst_ds }}',50)
    and dt<='{{ execution_date | cst_ds }}'
   ) ,

terminal_sign_all_detail as (
select
       billcode   --运单号
       ,end_agent_code --末端网点所属代理区code
       ,end_agent_name  --末端网点所属代理区
       ,end_center_code  --末端中心code
       , end_center_name  --末端中心name
       ,final_sign_network_code --规划签收网点code
       ,final_sign_network_name  --规划签收网点名字
       ,end_provider_id   --末端所属省id
       ,end_provider_name  --末端所属省
       ,end_city_id   --末端网点所属城市id
       ,end_city_name  --末端网点所属城市
       ,end_area_id   --末端网点所在区id
       ,end_area_name --末端网点所在区
       ,third_code  --面单三段码
       ,end_center_send_time  --末端中心发件时间
       ,deliver_user_code  --出仓派件员code
       ,final_sign_user_code  --最后签收派件员code
       ,final_sign_user_name   --最后签收派件员
       ,final_plan_sign_time  --规划签收时间
       ,aging_sign_time   --时效签收时间
       ,actual_sign_time  --实际签收时间
       ,end_network_arrival_time  --末端网点到件时间
       ,difficult_type   --问题件类型
       ,difficult_time --问题件时间
       ,deliver_time  --出仓时间
       ,center_scan_user_code  --中心扫描员
       ,site_scan_user_code  --网点扫描员
       ,deliver_scan_user_code  --出仓扫描员code
       ,if(deliver_check_flag=7,1,0  ) as is_arrival_send
       ,end_franchisee_code   --加盟商code
       ,end_franchisee_name   --加盟商
       ,if(print_time is not null,1,2) as is_reback_transfer    --是否退转件
       ,print_time  as reback_print_time     --面单打印时间
       ,send_next_station_code   --下一站code
       ,send_next_station_name  --下一站名字
       ,if(send_next_station_code=actual_sign_network_code,0,1) as is_eq   --下一站和实际签收是否相同
       ,dt
  from jms_dwd.dwd_terminal_sign_all_detail_new_dt
  where dt>=date_sub('{{ execution_date | cst_ds }}',29)
    and dt<='{{ execution_date | cst_ds }}'
    and aging_sign_time > end_center_send_time
),

sys_tail_code as (
    select
       b.staff_code,
       b.staff_name,a.id,
       a.code,
       a.name,
       a.network_code,
       a.network_id
    from jms_dim.dim_lmdm_sys_tail_code a
    join jms_dim.dim_lmdm_sys_tail_code_staff b
    on a.id=b.tail_id
    where a.is_delete=1
    and a.is_enable=1
   ),

 lmdm_sys_staff as (
   select
        id,                --id
        code,              --工号
        name,              --姓名
        is_enable,         --是否启用
        is_delete,         --是否被删除
        incumbency_status  --在职离职
   from jms_dim.dim_mysql_lmdm_sys_staff
   )

, tmp_resut_pre as (
--转运直送明细
select /*+ BROADCAST(lmdm_sys_staff)*/
        a.waybill_no                                             --运单号
       ,a.package_code                                           --包号
       ,a.scan_time                                              --扫描时间
       ,b.package_type                                           --包类型
       ,b.third_package_code                                     --包三段码
       ,b.second_package_code                                    --包二段吗
       ,c.report_time                                            --错分上报时间
      ,if(c.waybill_no is not null or b.center_code !=d.send_next_station_code ,1,0) as is_ass               --是否错分
       ,if(is_reback_transfer=2,d.end_provider_id,network.provider_id) as end_provider_id                                        --末端网点所属省ID
       ,if(is_reback_transfer=2,d.end_provider_name,network.provider_desc) as end_provider_name                                      --末端网点所属省
       ,if(is_reback_transfer=2,d.end_city_id,network.city_id) as end_city_id                                            --末端网点所属城市id
       ,if(is_reback_transfer=2,d.end_city_name,network.city_desc) as end_city_name                                          --末端网点所属城市
       ,if(is_reback_transfer=2,d.end_area_id,network.area_id) as end_area_id                                            --末端网点所属区县ID
       ,if(is_reback_transfer=2,d.end_area_name,network.area_desc) as end_area_name                                          --末端网点所属区县
       ,if(is_reback_transfer=2,d.end_agent_code,network.agent_code) as end_agent_code                                         --末端代理区code
       ,if(is_reback_transfer=2,d.end_agent_name,network.agent_name) as end_agent_name                                         --末端代理区名字
       ,a.scan_site_code as end_center_code                      --末端中心code
       ,a.scan_site as  end_center_name                          --末端中心名字
       ,d.end_network_arrival_time                               --末端网点到件时间
       ,if(is_reback_transfer=2,d.final_sign_network_code,b.center_code) as final_sign_network_code                                --规划签收网点code
       ,if(is_reback_transfer=2,d.final_sign_network_name,b.center_name) as final_sign_network_name                                --规划签收网点
       ,d.third_code                                             --运单三段码
       ,e.code                                                   --三段码维护的三段码(出仓单元区域号)
       ,if(d.third_code=e.code,1,0) as is_right_third_code       --三段码是否准确
       ,d.deliver_time                                           --出仓时间
       ,d.aging_sign_time                                        --时效签收时间
       ,d.final_plan_sign_time                                   --规划签收时间
       ,d.actual_sign_time                                       --实际签收时间
       ,d.end_center_send_time                                   --末端中心发件时间
       ,d.difficult_type                                         --问题件类型
       ,d.difficult_time                                         --问题件时间
       ,d.deliver_scan_user_code                                 --出仓扫描员code
       ,staff3.name as deliver_scan_user                         --出仓扫描员
       ,d.deliver_user_code                                      --出仓派件员code
       ,staff4.name as deliver_user                              --出仓派件员
       ,d.site_scan_user_code                                    --网点扫描员
       ,staff2.name as site_scan_user_name                       --网点扫描员
       ,d.center_scan_user_code                                   --中心扫描员
       ,staff1.name as center_scan_user_name                     --中心扫描员
       ,if(d.difficult_type='有发未到',1,0) as is_send_noarrival --是否是有发未到
       ,round(if(is_reback_transfer = 1 or if(c.waybill_no is not null or b.center_code !=d.send_next_station_code ,1,0)=1 ,0,(unix_timestamp(d.aging_sign_time) -unix_timestamp(d.end_center_send_time))/3600),2) as center_send_sign_hours  --发签时长
       ,round((unix_timestamp(d.end_network_arrival_time) -unix_timestamp(d.end_center_send_time))/3600,2) as arrival_send_hours  --到发时长
       ,round(if(is_reback_transfer = 1,0,(unix_timestamp(d.aging_sign_time) -unix_timestamp(d.deliver_time))/3600),2) as deliver_sign_hours--派签时长
       ,d.is_arrival_send
       ,d.dt date_time
       ,if(is_reback_transfer=2,d.end_franchisee_code,null) as end_franchisee_code   --加盟商code
       ,if(is_reback_transfer=2,d.end_franchisee_name,null) as end_franchisee_name   --加盟商
       ,is_reback_transfer  --是否退转件  1：是 2：否
       ,reback_print_time   --退件打印时间
       ,b.center_code as send_next_station_code   --下一站code
       ,b.center_name as send_next_station_name  --下一站名字
       -- ,row_number() over(partition by a.waybill_no order by a.scan_time asc) rn
       ,to_date(a.scan_time) as dt
from tab_barscan_bagging a
join third_package_list b
  on a.package_code = b.package_number
left join terminal_sign_all_detail d
  on a.waybill_no=d.billcode
-- and a.scan_site_code=d.end_center_code
left join ass_address_audit c
       on a.waybill_no=c.waybill_no
left join sys_tail_code e
       on d.final_sign_network_code=e.network_code
      and d.deliver_scan_user_code=e.staff_code
left join lmdm_sys_staff staff1
       on d.center_scan_user_code=staff1.code
left join lmdm_sys_staff staff2
       on d.site_scan_user_code=staff2.code
left join lmdm_sys_staff staff3
       on d.deliver_scan_user_code=staff3.code
left join lmdm_sys_staff staff4
       on d.deliver_user_code=staff4.code
left join jms_dim.dim_network_whole_massage network on b.center_code = network.code
)


insert overwrite table jms_dm.dm_terminal_tran_direct_delivery_detail_dt partition(dt)
select
   waybill_no                                             --运单号
  ,package_code                                           --包号
  ,scan_time                                              --扫描时间
  ,package_type                                           --包类型
  ,third_package_code                                     --包三段码
  ,second_package_code                                    --包二段吗
  ,report_time                                            --错分上报时间
  ,is_ass               --是否错分
  ,end_provider_id                                        --末端网点所属省ID
  ,end_provider_name                                      --末端网点所属省
  ,end_city_id                                            --末端网点所属城市id
  ,end_city_name                                          --末端网点所属城市
  ,end_area_id                                            --末端网点所属区县ID
  ,end_area_name                                          --末端网点所属区县
  ,end_agent_code                                         --末端代理区code
  ,end_agent_name                                         --末端代理区名字
  ,end_center_code                      --末端中心code
  ,end_center_name                          --末端中心名字
  ,end_network_arrival_time                               --末端网点到件时间
  ,final_sign_network_code                                --规划签收网点code
  ,final_sign_network_name                                --规划签收网点
  ,third_code                                             --运单三段码
  ,code                                                   --三段码维护的三段码(出仓单元区域号)
  ,is_right_third_code       --三段码是否准确
  ,deliver_time                                           --出仓时间
  ,aging_sign_time                                        --时效签收时间
  ,final_plan_sign_time                                   --规划签收时间
  ,actual_sign_time                                       --实际签收时间
  ,end_center_send_time                                   --末端中心发件时间
  ,difficult_type                                         --问题件类型
  ,difficult_time                                         --问题件时间
  ,deliver_scan_user_code                                 --出仓扫描员code
  ,deliver_scan_user                         --出仓扫描员
  ,deliver_user_code                                      --出仓派件员code
  ,deliver_user                              --出仓派件员
  ,site_scan_user_code                                    --网点扫描员
  ,site_scan_user_name                       --网点扫描员
  ,center_scan_user_code                                   --中心扫描员
  ,center_scan_user_name                     --中心扫描员
  ,is_send_noarrival --是否是有发未到
  ,center_send_sign_hours  --发签时长
  ,arrival_send_hours  --到发时长
  ,deliver_sign_hours--派签时长
  ,is_arrival_send
  ,dt date_time
  ,end_franchisee_code   --加盟商code
  ,end_franchisee_name   --加盟商
  ,is_reback_transfer  --是否退转件  1：是 2：否
  ,reback_print_time   --退件打印时间
  ,send_next_station_code   --下一站code
  ,send_next_station_name  --下一站名字
  ,dt
from tmp_resut_pre
-- where rn = 1
distribute by dt,pmod(hash(rand()),5)
;